using System;
using System.Collections.Generic;
using System.Text;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;


namespace getdbobject2
{
    class getdbobject2
    {
        static TSourceTokenList lcTableTokens = new TSourceTokenList(false);
        static TSourceTokenList lcFieldTokens = new TSourceTokenList(false);

        static void Main(string[] args)
        {


            /* These C# code was generated from SQL automatically by SQL Pretty Printer at www.wangz.net */

            StringBuilder inputsql1 = new StringBuilder("SELECT c.FirstName + ' ' + c.LastName AS Employee,\n");
            inputsql1.Append(" Datepart(MONTH,Soh.OrderDate) AS OrderMonthnum,\n");
            inputsql1.Append(" ps.Name AS SubCat,\n");
            inputsql1.Append(" SUM(Sod.LineTotal) AS Sales,\n");
            inputsql1.Append(" Soh.SaleSorderNumber,\n");
            inputsql1.Append(" p.Name AS Product,\n");
            inputsql1.Append(" SUM(Sod.Orderqty) AS Orderqty,\n");
            inputsql1.Append(" Sod.UnitPrice,\n");
            inputsql1.Append(" pc.Name AS ProdCat\n");
            inputsql1.Append(" FROM Sales.SaleSorderHeader Soh\n");
            inputsql1.Append(" INNER JOIN Sales.SaleSorderDetail Sod\n");
            inputsql1.Append(" ON Soh.SaleSordeRid = Sod.SaleSordeRid\n");
            inputsql1.Append(" INNER JOIN Sales.SalesPerson sp\n");
            inputsql1.Append(" ON Soh.SalesPersonId = sp.SalesPersonId\n");
            inputsql1.Append(" INNER JOIN HumanResources.Employee e\n");
            inputsql1.Append(" ON sp.SalesPersonId = e.EmployeeId\n");
            inputsql1.Append(" INNER JOIN Person.Contact c\n");
            inputsql1.Append(" ON e.ContactId = c.ContactId\n");
            inputsql1.Append(" INNER JOIN Production.Product p\n");
            inputsql1.Append(" ON Sod.ProductId = p.ProductId\n");
            inputsql1.Append(" INNER JOIN Production.ProductSubCategory ps\n");
            inputsql1.Append(" ON p.ProductSubCategoryId = ps.ProductSubCategoryId\n");

            /* following sql code was commented in order to make this demo works under
            * trail version of general sql parser which has a query size limitation of 1000 characters

            inputsql1.Append(" INNER JOIN Production.ProductCategory pc\n");
            inputsql1.Append(" ON ps.ProductCategoryId = pc.ProductCategoryId\n");
            inputsql1.Append(" WHERE (Datepart(YEAR,Soh.OrderDate) = @ReportYear)\n");
            inputsql1.Append(" AND (Datepart(MONTH,Soh.OrderDate) = @ReportMonth)\n");
            inputsql1.Append(" AND (Soh.SalesPersonId = @EmpID)\n");
            inputsql1.Append(" GROUP BY c.FirstName + ' ' + c.LastName,Datepart(MONTH,Soh.OrderDate),\n");
            inputsql1.Append(" Soh.SaleSorderNumber,p.Name,ps.Name,Sod.UnitPrice,\n");
            inputsql1.Append(" pc.Name\n");
            */

            /* End of code generated by SQL Pretty Printer at www.wangz.net */

            String searchstr = "SubCat"; //replace this value to what you like to search in the sql

            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
            sqlparser.SqlText.Text = inputsql1.ToString();
            int ret = sqlparser.Parse();
            if (ret == 0)
            {
                TSelectSqlStatement sqlstmt;
                sqlstmt = (TSelectSqlStatement)sqlparser.SqlStatements[0];
                // check searchstr is table or table alias
                Console.WriteLine("Tables in this sql: " + sqlstmt.Tables.Count());
                searchtable(sqlstmt.Tables, searchstr, true);

                // check field or field alias
                Console.WriteLine("Fields in this sql: " + sqlstmt.Fields.Count());
                searchfield(sqlstmt.Tables, sqlstmt.Fields, searchstr);
            }
            else
            {
                Console.WriteLine(sqlparser.ErrorMessages);
            }

            return;
        }

        public static void searchtable(TLzTableList pTables, String pStr, Boolean listTable)
        {
            for (int k = 0; k < pTables.Count(); k++)
            {
                if (listTable)
                    Console.WriteLine("prefix: " + pTables[k].TablePrefix.ToString() + "\tname: " + pTables[k].TableName.ToString() + "\t\t alias: " + pTables[k].TableAlias.ToString());
                if (pTables[k].TableName.ToString().ToLower().CompareTo(pStr.ToLower()) == 0)
                {
                    Console.WriteLine(pStr + " is table name with prefix: " + pTables[k].TablePrefix.ToString());
                }

                if (pTables[k].TableAlias.ToString().ToLower().CompareTo(pStr.ToLower()) == 0)
                {
                    Console.WriteLine(pStr + " is table alias of table " + pTables[k].TableName.ToString() + " with prefix " + pTables[k].TablePrefix.ToString());
                }
            }
        }

        public static void searchfield(TLzTableList pTables, TLzFieldList pFields, String pStr)
        {
            for (int k = 0; k < pFields.Count(); k++)
            {
                Console.WriteLine("prefix: " + pFields[k].FieldPrefix.ToString() + "\tname: " + pFields[k].FieldName.ToString() + "\talias: " + pFields[k].FieldAlias.ToString());

                if (pFields[k].FieldName.ToString().ToLower().CompareTo(pStr.ToLower()) == 0)
                {
                    Console.WriteLine(pStr + " is field name with prefix " + pFields[k].FieldPrefix.ToString());
                    if (pFields[k].FieldPrefix.ToString().Length > 0)
                    {
                        searchtable(pTables, pFields[k].FieldPrefix.ToString(), false);
                    }
                    fielddetail(pTables, pFields[k]);
                }

                if (pFields[k].FieldAlias.ToString().ToLower().CompareTo(pStr.ToLower()) == 0)
                {
                    Console.WriteLine(pStr + " is field alias of " + pFields[k].FieldName.ToString() + " with prefix " + pFields[k].FieldPrefix.ToString());
                    if (pFields[k].FieldPrefix.ToString().Length > 0)
                    {
                        searchtable(pTables, pFields[k].FieldPrefix.ToString(), false);
                    }
                    fielddetail(pTables, pFields[k]);
                }
            }
        }

        public static void fielddetail(TLzTableList pTables, TLzField pField)
        {
            switch (pField.FieldType)
            {
                case TLzFieldType.lftExpression:
                    if (pField.FieldExpr.oper == TLzOpType.Expr_FuncCall) //field is a function
                    {
                        TLz_FuncCall func;
                        func = (TLz_FuncCall)pField.FieldExpr.lexpr;
                        Console.WriteLine("Function: " + func.FunctionName);
                        // check arguments of this function
                        for (int k = 0; k < func.args.Count(); k++)
                        {
                            if (func.args[k] is TSourceToken)
                            {

                                TSourceToken st = (TSourceToken)func.args[k];
                                if ((st.DBObjType == TDBObjType.ttObjField) || (st.DBObjType == TDBObjType.ttObjFieldAlias))
                                {
                                    Console.WriteLine("Field: " + st.AsText);
                                }
                                if ((st.DBObjType == TDBObjType.ttObjTable) || (st.DBObjType == TDBObjType.ttObjTableAlias))
                                {
                                    Console.WriteLine("Table: " + st.AsText);
                                    searchtable(pTables, st.AsText, false);
                                }

                            }
                            else
                            {
                                lcTableTokens.Clear();
                                lcFieldTokens.Clear();
                                GetDbObjectsFromNode((TLz_Node)(func.args[k]));
                                for (int l = 0; l < lcTableTokens.Count(); l++)
                                {
                                    Console.WriteLine("Table: " + lcTableTokens[l].AsText);
                                    searchtable(pTables, lcTableTokens[l].AsText, false);
                                }
                                for (int m = 0; m < lcFieldTokens.Count(); m++)
                                {
                                    Console.WriteLine("Field: " + lcFieldTokens[m].AsText);
                                }
                            }
                        }

                    }
                    break;
                case TLzFieldType.lftSubquery:
                    Console.WriteLine("subquery");
                    TSelectSqlStatement st1;
                    st1 = (TSelectSqlStatement)pField.SubQuery;
                    Console.WriteLine(st1.AsText);
                    break;
            }

        }

        public static void GetDbObjectsFromNode(TLz_Node pNode)
        {
            //Fetch table and field objects into lcTableTokens,lcFieldTokens

            lcTableTokens.Clear();
            lcFieldTokens.Clear();

            TLzGetDbObjectsVisitor av = new TLzGetDbObjectsVisitor();
            av.ParseTree = pNode;
            av.TableTokens = lcTableTokens;
            av.FieldTokens = lcFieldTokens;
            av.Doit();

        }

    }
}